package project.dao;
import java.sql.ResultSet;
import java.sql.Savepoint;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import models.util.MySqlDataAccessHelper;
import project.model.PrProject;

public class ProjectDAO {
	MySqlDataAccessHelper mysql;
	
	public ProjectDAO(){
		mysql = new MySqlDataAccessHelper();
	}
	
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public static List getprolist( int limit, int pageN,String database) {
		MySqlDataAccessHelper mysql= new MySqlDataAccessHelper();
		List map = new ArrayList();
		ArrayList<PrProject> list = new ArrayList<PrProject>();
		int count = 0;
		try{
			mysql.open(database);
			String sql="select count(pr_project.IDProject) as sl from pr_project";
			
			ResultSet rs = mysql.executeQuery(sql);
			while(rs.next()){
				count=rs.getInt("sl");
			}
			rs.close();
			
			sql="select * from pr_project limit "+limit * (pageN - 1)+","+limit;
			rs=mysql.executeQuery(sql);
			PrProject pr;
			while(rs.next()){
				pr=new PrProject();
				pr.setIdProject(rs.getString("IDProject"));
				pr.setNameProject(rs.getString("NameProject"));
				pr.setIdManager(rs.getString("IDManager"));
				pr.setDescription(rs.getString("Description"));
				pr.setDateComplete(rs.getDate("DateComplete"));
				pr.setDateStart(rs.getDate("DateStart"));
				pr.setStatus(rs.getInt("Status"));
				list.add(pr);
			}
			rs.close();
			
			map.add(list);
			map.add(count);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				mysql.close();
			}catch(Exception ee){
				ee.printStackTrace();
			}
		}
		return map;
	}
	
	public ArrayList<PrProject> getAllProjectAdmin(String database){
		ArrayList<PrProject> list=new ArrayList<PrProject>();
		String sql="select * from pr_project";
		try {
			mysql.open(database);
			ResultSet rs = mysql.executeQuery(sql);
			PrProject pr;
			while (rs.next()) {
				pr=new PrProject();			
				pr.setIdProject(rs.getString("IDProject"));
				pr.setNameProject(rs.getString("NameProject"));
				pr.setIdManager(rs.getString("IDManager"));
				pr.setDescription(rs.getString("Description"));
				pr.setDateComplete(rs.getDate("DateComplete"));
				pr.setDateStart(rs.getDate("DateStart"));
				pr.setStatus(rs.getInt("Status"));
				list.add(pr);
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				mysql.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	
	public PrProject getOneProjectAdmin(String id,String database){
		PrProject pr=new PrProject();
		String sql="select * from pr_project where pr_project.IDProject='"+id+"'";
		try {
			mysql.open(database);
			ResultSet rs =mysql.executeQuery(sql);
				
			if (rs.next()) {
						
				pr.setIdProject(rs.getString("IDProject"));
				pr.setNameProject(rs.getString("NameProject"));
				pr.setIdManager(rs.getString("IDManager"));
				pr.setDescription(rs.getString("Description"));
				pr.setDateComplete(rs.getDate("DateComplete"));
				pr.setDateStart(rs.getDate("DateStart"));
				pr.setStatus(rs.getInt("Status"));
				
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				mysql.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return pr;
	}
	
	public int deletProjectInfo(PrProject pr,String database) throws Exception{
		int kq=-1;
		MySqlDataAccessHelper mysql =new MySqlDataAccessHelper();
		
		try{
			mysql.open(database);
			String sql="delete from pr_project where pr_project.IDProject='"+pr.getIdProject()+"'";			
			kq = mysql.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return kq;
	}
	
	public int updateProjectInfo(PrProject pr,String database) throws Exception{
		int kq=-1;
		MySqlDataAccessHelper mysql =new MySqlDataAccessHelper();
		
		try{
			mysql.open(database);
			String sql="update pr_project " +
					"set pr_project.NameProject='"+pr.getNameProject()+"'," +
							"pr_project.Description='"+pr.getDescription()+"'," +
									"pr_project.IDManager='"+pr.getIdManager()+"'," +
											"pr_project.DateStart='"+pr.getdS()+"'," +
													"pr_project.DateComplete='"+pr.getdC()+"'," +
															"pr_project.Status="+pr.getStatus()+" "+
					" where pr_project.IDProject='"+pr.getIdProject()+"'";
			
			kq = mysql.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return kq;
	}
	
	
	public boolean checkExitInsert(PrProject pr,String database){
		boolean kq=false;
		MySqlDataAccessHelper mysql =new MySqlDataAccessHelper();
		try{
			mysql.open(database);
			String sql="select pr_project.IDProject " +
					"from pr_project where pr_project.NameProject='"+pr.getNameProject()+"'";
			ResultSet rs=mysql.executeQuery(sql);
			if(rs.next()){
				kq=true;
			}
			rs.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		
		return kq;
	}
	
	public boolean checkExitUpdate(PrProject pr,String database){
		boolean kq=false;
		MySqlDataAccessHelper mysql =new MySqlDataAccessHelper();
		try{
			mysql.open(database);
			String sql="select pr_project.IDProject from pr_project where pr_project.NameProject='"+pr.getNameProject()+"' and pr_project.IDProject != '"+pr.getIdProject()+"'";
			ResultSet rs=mysql.executeQuery(sql);
			if(rs.next()){
				kq=true;
			}
			rs.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		
		return kq;
	}
	
	public int insertProjectInfo(PrProject pr, String database){
		int kq=-1;
		MySqlDataAccessHelper mysql =new MySqlDataAccessHelper();
		try{
			mysql.open(database);
			//SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
			
			String sql="insert into pr_project (IDProject,NameProject,Description,IDManager,DateStart,DateComplete,Status) " +
					"values(trim("+pr.getIdProject()+"),'"+pr.getNameProject()+"','"+pr.getDescription()+"','"+pr.getIdManager()+"','"+pr.getdS()+"','"+pr.getdC()+"',"+pr.getStatus()+")";
			kq=mysql.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return kq;
	}
	
	
	public int deletProjectAllInfo(PrProject pr,String database) throws Exception{
		int kq=-1;
		String sidlog="";
		String sidcom="";
		String sidem="";
		String siderr="";
		
		sidlog=pr.getIdLog(database);
		sidcom=pr.getIdCom(database);
		sidem=pr.getIdEm(database);
		siderr=pr.getIdErr(database);
		
		MySqlDataAccessHelper mysql =new MySqlDataAccessHelper();		
		try{
			
			
			mysql.open(database);
			
			java.sql.Connection conn = mysql.getConnection();
			Statement stmt = conn.createStatement();
			conn.setAutoCommit(false);
			Savepoint sv1 = conn.setSavepoint("svpt1");
			
			try{
				
				
				if(sidlog.equals(null)==false && sidlog.equals("")==false)
					kq = stmt.executeUpdate("delete from pr_logchange where pr_logchange.IDLogChange in ("+sidlog+")");
								
				if(sidcom.equals(null)==false && !sidcom.equals(""))
					kq = stmt.executeUpdate("delete from pr_comment where pr_comment.IDComment in ("+sidcom+")");
				
				if(siderr.equals(null)==false && !siderr.equals(""))
					kq= stmt.executeUpdate("delete from pr_testproject where pr_testproject.IDTest in ("+siderr+")");
				
				if(sidem.equals(null)==false && !sidem.equals(""))
					kq= stmt.executeUpdate("delete from pr_composition where pr_composition.IDEmployee in ("+sidem+")");
				
				kq= stmt.executeUpdate("delete from pr_project where pr_project.IDProject='"+pr.getIdProject()+"'");
				
				mysql.getConnection().commit();
			}catch(Exception e){
				try{
					System.out.println(" đã rollback ");
					mysql.getConnection().rollback(sv1);
				}catch(Exception ex){
					System.out.println("error delete");
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return kq;
	}
}
